// JavaScript Document
	var func = {};
	func.webdb = {};
	func.webdb.db = null;
    func.webdb.console = null;
	
	func.webdb.setConsole = function(consoleName){
		console = document.getElementById(consoleName);
		if(console != null){
			func.webdb.printInConsole("<li> console established </li>");
		}else{
			alert("Can't find a element named " + consoleName + ". Console can't be established.");
		}
	}
	
	func.webdb.printInConsole = function(msg){
		if(console != null){
			console.innerHTML +=  msg;
		}
	}
	//Open the database
	func.webdb.openDB = function() {
		console.log( "creating database");
		var dbSize = 5 * 1024 * 1024; // 5MB
		func.webdb.db = openDatabase("agricultureDB", "1.0", "agricultureDB", dbSize);
		if (func.webdb.db != null){
		    console.log("database created");
		}
	}

	//Create table and fill in dummy data
	func.webdb.createTables = function() {
	var db = func.webdb.db;
	if (db != null) {
		console.log(" request data from server");
				
		db
				.transaction(function(tx) {
			        ////////////////////////////////////////////////////
					// Request and store crops table
					///////////////////////////////////////////////////
					console.log(" creating crop table");
					func.server.requestData("crops");
					var dataLen = func.server.data.length;

					tx
							.executeSql("CREATE TABLE IF NOT EXISTS crops(crop_id INTEGER, crop_name TEXT)");
					tx.executeSql("DELETE FROM crops", [], null,
							func.webdb.onError);
					for ( var i = 0; i < dataLen; i++) {
						var temp_id = func.server.data[i].crop_id;
						var temp_name = func.server.data[i].crop_name;
						console.log("crop id is " + temp_id + ", crop name is "
								+ temp_name);
						tx
								.executeSql(
										"INSERT INTO crops(crop_id, crop_name) VALUES (?, ?)",
										[ temp_id, temp_name ], null,
										func.webdb.onError);
					}

					// ////////////////////////////////////////////////
					// Request and store crop stage table
					// ////////////////////////////////////////////////
					console.log("creating crop stage table");
					func.server.requestData("stage");
					var dataLen = func.server.data.length;

					tx
							.executeSql("CREATE TABLE IF NOT EXISTS crops_stage(stage_id INTEGER, stage_name TEXT)");
					tx.executeSql("DELETE FROM crops_stage", [], null,
							func.webdb.onError);

					for ( var i = 0; i < dataLen; i++) {
						var temp_id = func.server.data[i].stage_id;
						var temp_name = func.server.data[i].stage_name;
						console.log("stage id is " + temp_id
								+ ", stage name is " + temp_name);
						tx
								.executeSql(
										"INSERT INTO crops_stage(stage_id, stage_name) VALUES (?, ?)",
										[ temp_id, temp_name ], null,
										func.webdb.onError);
					}
					
					// ////////////////////////////////////////////////
					// Request and store weed table
					// ////////////////////////////////////////////////
					console.log("creating weed table");
					func.server.requestData("weeds");
					var dataLen = func.server.data.length;

					tx
							.executeSql("CREATE TABLE IF NOT EXISTS weeds(weed_id INTEGER, weed_name TEXT, weed_info TEXT)");
					tx.executeSql("DELETE FROM weeds", [], null,
							func.webdb.onError);

					for ( var i = 0; i < dataLen; i++) {
						var temp_id = func.server.data[i].weed_id;
						var temp_name = func.server.data[i].weed_name;
						var temp_info = func.server.data[i].weed_info;
						console.log("weed id is " + temp_id
								+ ", weed name is " + temp_name
								+ ", weed info is " + temp_info);
						tx
								.executeSql(
										"INSERT INTO weeds(weed_id, weed_name, weed_info) VALUES (?, ?, ?)",
										[ temp_id, temp_name, temp_info], null,
										func.webdb.onError);
					}
					
					// ////////////////////////////////////////////////
					// Request and store weed stage table
					// ////////////////////////////////////////////////
					console.log("creating weed stage table");
					func.server.requestData("stage");
					var dataLen = func.server.data.length;

					tx
							.executeSql("CREATE TABLE IF NOT EXISTS weeds_stage(stage_id INTEGER, stage_name TEXT)");
					tx.executeSql("DELETE FROM weeds_stage", [], null,
							func.webdb.onError);

					for ( var i = 0; i < dataLen; i++) {
						var temp_id = func.server.data[i].stage_id;
						var temp_name = func.server.data[i].stage_name;
						console.log("stage id is " + temp_id
								+ ", stage name is " + temp_name);
						tx
								.executeSql(
										"INSERT INTO weeds_stage(stage_id, stage_name) VALUES (?, ?)",
										[ temp_id, temp_name], null,
										func.webdb.onError);
					}
					
					// ////////////////////////////////////////////////
					// Request and store weed herb table
					// ////////////////////////////////////////////////
					console.log("creating weed herbicide table");
					func.server.requestData("weed_herbicide");
					var dataLen = func.server.data.length;

					tx
							.executeSql("CREATE TABLE IF NOT EXISTS weed_herbicide(w_h_id INTEGER, " +
									"weed_id INTEGER, herb_id INTEGER, " +
									"w_stid_min INTEGER, w_stid_max INTEGER)");
					
					tx.executeSql("DELETE FROM weed_herbicide", [], null,
							func.webdb.onError);

					for ( var i = 0; i < dataLen; i++) {
						var temp_id_wh = func.server.data[i].w_h_id;
						var temp_id_w = func.server.data[i].weed_id;
						var temp_id_h = func.server.data[i].herb_id;
						var temp_id_min = func.server.data[i].w_stid_min;
						var temp_id_max = func.server.data[i].w_stid_max;
						console.log(
								"key id is " + temp_id_wh
								+ ", weed id is " + temp_id_w
								+ ", herb id is " + temp_id_h
								+ ", min stg id is" + temp_id_min,
								+ ", max stg id is" + temp_id_max);
						tx
								.executeSql(
										"INSERT INTO weed_herbicide(w_h_id, weed_id, herb_id, w_stid_min, w_stid_max) " +
										"VALUES (?, ?, ?, ?, ?)",
										[ temp_id_wh, temp_id_w, temp_id_h, temp_id_min, temp_id_max], null,
										func.webdb.onError);
					}
					
					// ////////////////////////////////////////////////
					// Request and store crop product table
					// ////////////////////////////////////////////////
					console.log("creating crop product table");
					func.server.requestData("crop_product");
					var dataLen = func.server.data.length;

					tx
							.executeSql("CREATE TABLE IF NOT EXISTS crop_product(c_P_id INTEGER, " +
									"crop_id INTEGER, prod_id INTEGER, " +
									"cr_stid_min INTEGER, cr_stid_max INTEGER, rate TEXT)");
					
					tx.executeSql("DELETE FROM crop_product", [], null,
							func.webdb.onError);

					for ( var i = 0; i < dataLen; i++) {
						var temp_id_cp = func.server.data[i].c_P_id;
						var temp_id_c = func.server.data[i].crop_id;
						var temp_id_p = func.server.data[i].prod_id;
						var temp_id_min = func.server.data[i].cr_stid_min;
						var temp_id_max = func.server.data[i].cr_stid_max;
						var temp_rate = func.server.data[i].rate;
						console.log(
								"key id is " + temp_id_cp
								+ ", weed id is " + temp_id_c
								+ ", herb id is " + temp_id_p
								+ ", min stg id is " + temp_id_min,
								+ ", max stg id is " + temp_id_max
								+ ", rate is " + temp_rate);
						tx
								.executeSql(
										"INSERT INTO crop_product(c_P_id, crop_id, prod_id, cr_stid_min, cr_stid_max, rate) " +
										"VALUES (?, ?, ?, ?, ?, ?)",
										[ temp_id_cp, temp_id_c, temp_id_p, temp_id_min, temp_id_max, temp_rate], null,
										func.webdb.onError);
					}
					
					// ////////////////////////////////////////////////
					// Request and store herbicide table
					// ////////////////////////////////////////////////
					console.log("creating herbicide table");
					func.server.requestData("herbicide");
					var dataLen = func.server.data.length;

					tx
							.executeSql("CREATE TABLE IF NOT EXISTS herbicide(herb_id INTEGER, " +
									"herb_name TEXT, herb_group INTEGER, " +
									"other_info TEXT)");
					
					tx.executeSql("DELETE FROM herbicide", [], null,
							func.webdb.onError);

					for ( var i = 0; i < dataLen; i++) {
						var temp_id = func.server.data[i].herb_id;
						var temp_name = func.server.data[i].herb_name;
						var temp_group = func.server.data[i].herb_group;
						var temp_info = func.server.data[i].other_info;
						
						console.log(
								"herb id is " + temp_id
								+ ", herb name is " + temp_name
								+ ", herb group is " + temp_group
								+ ", min stg id is " + temp_info);
						
						tx
								.executeSql(
										"INSERT INTO herbicide(herb_id, herb_name, herb_group, other_info) " +
										"VALUES (?, ?, ?, ?)",
										[ temp_id, temp_name, temp_group, temp_info], null,
										func.webdb.onError);
					}
					
					// ////////////////////////////////////////////////
					// Request and store product table
					// ////////////////////////////////////////////////
					console.log("creating products table");
					func.server.requestData("products");
					var dataLen = func.server.data.length;

					tx
							.executeSql("CREATE TABLE IF NOT EXISTS products(product_id INTEGER, " 
									+ "herb_id INTEGER, product_company TEXT, " + "product_name TEXT," 
									+ "product_ingredients TEXT, product_info TEXT)");
					
					tx.executeSql("DELETE FROM products", [], null,
							func.webdb.onError);

					for ( var i = 0; i < dataLen; i++) {
						var temp_id_p = func.server.data[i].product_id;
						var temp_id_h = func.server.data[i].herb_id;
						var temp_comp = func.server.data[i].product_company;
						var temp_name = func.server.data[i].product_name;
						var temp_ingredients = func.server.data[i].product_ingredients;
						var temp_info = func.server.data[i].product_info;
						
						console.log(
								"product id is " + temp_id_p
								+ ", herb name is " + temp_id_h
								+ ", product comp is " + temp_comp
								+ ", product name is " + temp_name
								+ ", ingredients is " + temp_ingredients
								+ ", product info is " + temp_info);
						
						tx
								.executeSql(
										"INSERT INTO products(product_id, herb_id, product_company, " +
										"product_name, product_ingredients, product_info) " +
										"VALUES (?, ?, ?, ?, ?, ?)",
										[ temp_id_p, temp_id_h, temp_comp, temp_name, temp_ingredients, temp_info], null,
										func.webdb.onError);
					}
					
					
				});
		
		// Create product_crop table
		console.log("creating product_crop table ");

		db
				.transaction(function(tx) {
					tx
							.executeSql(
									"CREATE TABLE IF NOT EXISTS product_crop(productID INTEGER, cropID INTEGER, tankMix INTEGER, stageIDMin INTEGER, stageIDMax INTEGER)",
									[], null, func.webdb.onError);

					// Clear data stored previewsly
					tx.executeSql("DELETE FROM product_crop", [], null,
							func.webdb.onError);

					// Fill in dummy data
					tx
							.executeSql(
									"INSERT INTO product_crop(productID, cropID, tankMix, stageIDMin, stageIDMax) VALUES (?, ?, ?, ?, ?)",
									[ 1, 1, 2, 2, 6 ], null, func.webdb.onError);
					tx
							.executeSql(
									"INSERT INTO product_crop(productID, cropID, tankMix, stageIDMin, stageIDMax) VALUES (?, ?, ?, ?, ?)",
									[ 2, 1, 2, 3, 6 ], null, func.webdb.onError);
					tx
							.executeSql(
									"INSERT INTO product_crop(productID, cropID, tankMix, stageIDMin, stageIDMax) VALUES (?, ?, ?, ?, ?)",
									[ 3, 1, 3, 1, 6 ], null, func.webdb.onError);
					tx
							.executeSql(
									"INSERT INTO product_crop(productID, cropID, tankMix, stageIDMin, stageIDMax) VALUES (?, ?, ?, ?, ?)",
									[ 4, 1, 1, 4, 6 ], null, func.webdb.onError);
				});
		console.log("product_crop table created");

		// Create product_weed table
		console.log("creating product_weed table");
		db = func.webdb.db;
		db
				.transaction(function(tx) {
					tx
							.executeSql(
									"CREATE TABLE IF NOT EXISTS product_weed(productID INTEGER, weedID INTEGER,  stageIDMin INTEGER, stageIDMax INTEGER)",
									[], null, func.webdb.onError);

					// Clear data
					tx.executeSql("DELETE FROM product_weed", [], null,
							func.webdb.onError);

					// Fill in dummy data
					tx
							.executeSql(
									"INSERT INTO product_weed(productID, weedID, stageIDMin, stageIDMax) VALUES (?, ?, ?, ?)",
									[ 1, 2, 2, 6 ], null, func.webdb.onError);
					tx
							.executeSql(
									"INSERT INTO product_weed(productID, weedID, stageIDMin, stageIDMax) VALUES (?, ?, ?, ?)",
									[ 2, 2, 3, 6 ], null, func.webdb.onError);
					tx
							.executeSql(
									"INSERT INTO product_weed(productID, weedID, stageIDMin, stageIDMax) VALUES (?, ?, ?, ?)",
									[ 3, 2, 1, 6 ], null, func.webdb.onError);
					tx
							.executeSql(
									"INSERT INTO product_weed(productID, weedID, stageIDMin, stageIDMax) VALUES (?, ?, ?, ?)",
									[ 5, 2, 4, 6 ], null, func.webdb.onError);
				});
		console.log("product_weed table created");
	} else {
		console.log("open agriculture database first");
	}
}
	
	//Error handling function 
	func.webdb.onError = function(tx, e){
		alert("There has been an error: " + e.message);
	}
	
	//------------------------------------------------------------------------------
	// Functions conduct the query
	//------------------------------------------------------------------------------
	func.webdb.queryResult =  null;
	func.webdb.isQueryFinished = false;
	
	func.webdb.storeRes = function(tx, rs){
		console.log("query returning");
		func.webdb.isQueryFinished = true;
		func.webdb.queryResult = rs;
		func.webdb.isQueryFinished = true;	
		console.log("query result is stored");	
	}
	
	func.webdb.query = function(str){
		var db = func.webdb.db;
		func.webdb.isQueryFinished = false;
		
		if(db != null) {
			console.log("query starting");
			db.transaction(function(tx){
				tx.executeSql("SELECT * FROM product_crop",[], 
				              func.webdb.storeRes, func.webdb.onError);
			});
			console.log("query conducted\n");
		}
	}
	//--------------------------------------------------------------------------------
	
	//Printout all tables
	func.webdb.showAllTables = function() {
		var db = func.webdb.db;
		if (db != null) {
			
			db.transaction(function(tx) {
				tx.executeSql("SELECT * FROM product_crop", [],
						func.webdb.renderProductCropTable, func.webdb.onError);
			});
			
			
			db.transaction(function(tx) {
				tx.executeSql("SELECT * FROM product_weed", [],
						func.webdb.renderProductWeedTable, func.webdb.onError);
			});
		}
	}

	//Print out product_crop table
	func.webdb.renderProductCropTable = function(tx, rs) {
		func.webdb.printInConsole("<li> printing product_crop table </li>");
		
		var rowOutput = "";
		for ( var i = 0; i < rs.rows.length; i++) {
			var row = rs.rows.item(i);
			rowOutput += "<li>[" 
			                   + row.productID + "," 
			                   + row.cropID + "," 
			                   + row.tankMix + "," 
			                   + row.stageIDMin + "," 
			                   + row.stageIDMax + "]</li>";
		}
		func.webdb.printInConsole(rowOutput);
	}
	
	//Print out product_weed table
	func.webdb.renderProductWeedTable = function(tx, rs) {
		func.webdb.printInConsole("<li> printing product_weed table </li>");
		
		var rowOutput = "";
		for ( var i = 0; i < rs.rows.length; i++) {
			var row = rs.rows.item(i);
			rowOutput += "<li>[" 
			                   + row.productID + "," 
			                   + row.weedID + "," 
			                   + row.stageIDMin + "," 
			                   + row.stageIDMax + "]</li>";
		}
		func.webdb.printInConsole(rowOutput);
	}
	
	//Execute the query
	func.webdb.theQuery = function(){
		var db = func.webdb.db;
		if(db != null) {
			db.transaction(function(tx) {
				tx.executeSql("SELECT productID FROM product_crop WHERE cropID = 1 AND stageIDMin < 5 AND stageIDMax > 5 AND productID IN (SELECT productID FROM product_weed WHERE weedID = 2 AND stageIDMin < 5 AND stageIDMax > 5)", [],
						func.webdb.renderProductIDTable, func.webdb.onError);
			});
		}
	}
	
	//Render the productID table return by the query
	func.webdb.renderProductIDTable = function(tx, rs){
		func.webdb.printInConsole("<li> printing productID table </li>");
		
		var rowOutput = "";
		for ( var i = 0; i < rs.rows.length; i++) {
			var row = rs.rows.item(i);
			rowOutput += "<li>[" + row.productID  + "]</li>";
		}
		func.webdb.printInConsole(rowOutput);
	}
	
	func.webdb.selectedCrop_id = null;
	func.webdb.selectedCrop_name = null;
	func.webdb.setCropSelection = function(crop_id, crop_name){
		func.webdb.selectedCrop_id = crop_id;
		func.webdb.selectedCrop_name = crop_name;
		
		console.log("crop id is set to " + func.webdb.selectedCrop_id);
	}
	
	func.webdb.selectedCropStage_id = null;
	func.webdb.selectedCropStage_name = null;
	func.webdb.setCropStageSelection = function(crop_stage_id, crop_stage_name){
		func.webdb.selectedCropStage_id = crop_stage_id;
		func.webdb.selectedCropStage_name = crop_stage_name;
		
		console.log("crop stage is set to " + func.webdb.selectedCropStage_id);
	}
	
	func.webdb.selectedWeed_id = null;
	func.webdb.selectedWeed_name = null;
	func.webdb.setWeedSelection = function(weed_id, weed_name){
		func.webdb.selectedWeed_id = weed_id;
		func.webdb.selectedWeed_name = weed_name;
		
		console.log("weed id is set to " + func.webdb.selectedWeed_id);
	}
	
	func.webdb.selectedWeedStage_id = null;
	func.webdb.selectedWeedStage_name = null;
	func.webdb.setWeedStageSelection = function(weed_stage_id, weed_stage_name){
		func.webdb.selectedWeedStage_id = weed_stage_id;
		func.webdb.selectedWeedStage_name = weed_stage_name;
		
		func.webdb.addWeed();
		
		console.log("weed stage is set to " + func.webdb.selectedWeedStage_id);
	}
	
	func.webdb.weedCart_id = new Array();
	func.webdb.weedCart_name = new Array();
	func.webdb.weedCart_stage_id = new Array();
	func.webdb.weedCart_stage_name = new Array();
	func.webdb.weedCart_checked = new Array();
	func.webdb.addWeed = function(){
		func.webdb.weedCart_id.push(func.webdb.selectedWeed_id);
		func.webdb.weedCart_name.push(func.webdb.selectedWeed_name);
		func.webdb.weedCart_stage_id.push(func.webdb.selectedWeedStage_id);
		func.webdb.weedCart_stage_name.push(func.webdb.selectedWeedStage_name);
		func.webdb.weedCart_checked.push(true);
	}
	
	func.webdb.removeWeed = function(id){
		var temp_id = new Array();
		var temp_name = new Array();
		var temp_stage_id = new Array();
		var temp_stage_name = new Array();
		var temp_checked = new Array();
		
		var cartLength = func.webdb.weedCart_id.length;
		for(var i = 0; i < cartLength; i++ ){
			if(i != id){
				temp_id.push(func.webdb.weedCart_id[i]);
				temp_name.push(func.webdb.weedCart_name[i]);
				temp_stage_id.push(func.webdb.weedCart_stage_id[i]);
				temp_stage_name.push(func.webdb.weedCart_stage_name[i]);
				temp_checked.push(func.webdb.weedCart_checked[i]);
				console.log("the " + i + "th element pushed" );
			}
		}
		
		
		func.webdb.weedCart_id = new Array();
		func.webdb.weedCart_name = new Array();
		func.webdb.weedCart_stage_id = new Array();
		func.webdb.weedCart_stage_name = new Array();
		func.webdb.weedCart_checked = new Array();
		
		
		func.webdb.weedCart_id = temp_id;
		func.webdb.weedCart_name = temp_name;
		func.webdb.weedCart_stage_id = temp_stage_id;
		func.webdb.weedCart_stage_name = temp_stage_name;
		func.webdb.weedCart_checked = temp_checked;
	}
	
	func.webdb.resetVariables = function(){
		func.webdb.selectedCrop_id = null;
		func.webdb.selectedCrop_name = null;
		func.webdb.selectedCropStage_id = null;
		func.webdb.selectedCropStage_name = null;
		func.webdb.selectedWeed_id = null;
		func.webdb.selectedWeed_name = null;
		func.webdb.selectedWeedStage_id = null;
		func.webdb.selectedWeedStage_name = null;
		var cartLength = func.webdb.weedCart_id.length;
		for(var i = 0; i < cartLength; i++){
			func.webdb.weedCart_id.pop();
			func.webdb.weedCart_name.pop();
			func.webdb.weedCart_stage_id.pop();
			func.webdb.weedCart_stage_name.pop();
			func.webdb.weedCart_checked.pop();
		}
	}
	
	
	func.webdb.setWeedCartCheck = function( index,  bool){
		
		func.webdb.weedCart_checked[index] = bool;
		console.log("checked state changed to" + "index =" + index + " bool =" + func.webdb.weedCart_checked[index]);
		for(var i=0; i < func.webdb.weedCart_checked.length; i++){
			console.log("the " + i + "th check status is " + func.webdb.weedCart_checked[i]);
		}
	}
	
	
	func.webdb.selectedHerbicide_id = null;
	func.webdb.selectedHerbicide_name = null;
	func.webdb.setHerbicideSelection = function(herb_id, herb_name){
		func.webdb.selectedHerbicide_id = herb_id;
		func.webdb.selectedHerbicide_name = herb_name;
		
		console.log("herb id is set to " + func.webdb.selectedHerbicide_id);
	}
	
	func.webdb.selectedProduct_index = null;
	func.webdb.selectedProduct_id = null;
	func.webdb.selectedProduct_name = null;
	func.webdb.selectedProduct_company = null;
	func.webdb.selectedProduct_ingredients = null;
	func.webdb.selectedProduct_info = null;
	func.webdb.setProductSelection = function(prod_index, prod_name, prod_id){
		func.webdb.selectedProduct_id = prod_id;
		func.webdb.selectedProduct_index = prod_index;
		func.webdb.selectedProduct_name = prod_name;
		func.webdb.selectedProduct_company = func.webdb.queryResult.rows.item(prod_index).product_company;
		func.webdb.selectedProduct_ingredients = func.webdb.queryResult.rows.item(prod_index).product_ingredients;
		func.webdb.selectedProduct_info = func.webdb.queryResult.rows.item(prod_index).product_info;
		console.log("prod id is set to " + func.webdb.selectedProduct_index);
	}
	//Simply clear the console when it is too messy
	func.clearConsole = function() {
		console.innerHTML = "";
	}
	
	//-------------------------------------------
	// functions to retrieve data from the server
	//-------------------------------------------
	func.server = {};
	func.server.xmlRequest = null;
	func.server.data = null;
	
	if (window.XMLHttpRequest) {// code for IE7+, Firefox, Chrome, Opera, Safari
		func.server.xmlRequest = new XMLHttpRequest();
	} else {// code for IE6, IE5
		func.server.xmlRequest = new ActiveXObject("Microsoft.XMLHTTP");
	}

    /*
	func.server.xmlRequest.onreadystatechange = function() {
				// document.getElementById("txtHint").innerHTML=xmlhttp.responseText;	
				//var person = eval('(' + xmlhttp.responseText + ')');
		if(func.server.xmlRequest.readyState == 4){
		    func.server.data = eval('(' + func.server.xmlRequest.responseText + ')');
			console.log("data updated\n");
		}
			
	};
	*/

	func.server.requestData = function(str){
		func.server.xmlRequest.open("GET",
				"http://128.233.110.147:8888/testDB/getuser.php?table=" + str,
				false);
		func.server.xmlRequest.send(null);
		func.server.data = eval('(' + func.server.xmlRequest.responseText + ')');
		//console.log(func.server.xmlRequest.responseText);
	}
	
